﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyHocPhi.Model;
using System.Data;
using System.Data.Entity.Infrastructure;
using System.Data.Entity;
namespace QuanLyHocPhi.BLL
{
    /// <summary>
    /// Xử lý nghiệp thu liên quan đến phiếu thu
    /// </summary>
    class BizPhieuThu
    {
        internal static int ListPhieuThuLoi()
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var dsPTLHP_SV = from e in context.LopHocPhan_SinhVienDbSet
                                     select e.MaPhieuThu;
                    var dsPTFromPhieuThuTable = (from e in context.PhieuThuDbSet
                                                 where !dsPTLHP_SV.Contains(e.MaPhieuThu)
                                                 select e).ToList();
                    foreach (var item in dsPTFromPhieuThuTable)
                        context.PhieuThuDbSet.Remove(item);
                    context.SaveChanges();
                    return dsPTFromPhieuThuTable.Count;
                }
            }
            catch
            {
                throw;
                //return false;
            }
        }


        /// <summary>
        /// Thêm mới một phiếu thu
        /// </summary>
        /// <param name="phieuThu"></param>
        /// <returns></returns>
        internal static bool AddNew_PhieuThu(PhieuThu phieuThu)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    context.PhieuThuDbSet.Add(phieuThu);
                    context.SaveChanges();
                    return true;
                }
            }
            catch
            {
                throw;
                //return false;
            }
        }

        /// <summary>
        /// Cập nhật thông tin về LopHocPhan_SinhVien không chuyên. 
        /// Cụ thể: thông tin về mã phiếu thu
        /// </summary>
        /// <param name="lopHocPhan_SinhVien"></param>
        /// <returns></returns>
        internal static bool Update_LopHocPhan_SinhVienKhongChuyen(LopHocPhan_SinhVien lopHocPhan_SinhVien)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var lopHP_SV = context.LopHocPhan_SinhVienDbSet.Where(e => e.MaSinhVien.Equals(lopHocPhan_SinhVien.MaSinhVien) && e.MaLopHocPhan.Equals(lopHocPhan_SinhVien.MaLopHocPhan)).FirstOrDefault();
                    lopHP_SV.MaPhieuThu = lopHocPhan_SinhVien.MaPhieuThu;
                    lopHP_SV.HeSoMienGiam = lopHocPhan_SinhVien.HeSoMienGiam;
                    context.SaveChanges();
                    return true;
                }
            }
            catch
            {
                throw;
            }
        }

        internal static bool Update_LopChuyenNganh_SinhVienChuyen(LopHocPhanChuyenNganh_SinhVien lopChuyenNganh_SinhVien)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var lopCN_SV = context.LopHocPhanChuyenNganh_SinhVienDbSet.Where(e => e.MaSinhVien.Equals(lopChuyenNganh_SinhVien.MaSinhVien) && e.MaLopHocPhanChuyenNganh.Equals(lopChuyenNganh_SinhVien.MaLopHocPhanChuyenNganh)).FirstOrDefault();
                    lopCN_SV.MaPhieuThu = lopChuyenNganh_SinhVien.MaPhieuThu;
                    context.SaveChanges();
                    return true;
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Lấy về thông tin phiếu thu dựa vào mã phiếu thu
        /// </summary>
        /// <param name="maPhieuThu"></param>
        /// <returns></returns>
        internal static PhieuThu GetPhieuThu(string maPhieuThu)
        {
            using (var context = new QLHPEntities())
            {
                var phieuThu = context.PhieuThuDbSet.Include(e => e.SinhVien).FirstOrDefault(e => e.MaPhieuThu.Equals(maPhieuThu));
                return phieuThu;
            }
        }

        internal static string MaxSoPhieuThuTheoQuyenSo(string quyenSo)
        {
            using (var context = new QLHPEntities())
            {
                long PhieuThuMax = 0;
                try
                {
                    var lst = context.Database.SqlQuery<long>
                        ("select max(cast(SoPhieuThu as bigint)) from PhieuThu where QuyenSo='" + quyenSo + "'").ToList();
                    if (lst.Count > 0)
                        PhieuThuMax = lst[0];
                    else
                        PhieuThuMax = 1;
                    return PhieuThuMax.ToString();
                }
                catch
                {
                    return "1";
                }
            }
        }

        /// <summary>
        /// Lấy danh sách phiếu thu theo loại
        /// </summary>
        /// <param name="key">Từ khóa cần tìm</param>
        /// <param name="indexLoai">
        /// 0: Quyển số. 1: Lớp học phần
        /// </param>
        /// <returns></returns>
        internal static List<PhieuThu> GetDanhSachPhieuThu(string key, int indexLoai)
        {
            using (var context = new QLHPEntities())
            {
                if (indexLoai == 0)
                {
                    //Quyển số
                    return context.PhieuThuDbSet.OrderBy(e => e.SinhVien.HoDem).OrderBy(e => e.SinhVien.Ten).Include(e => e.SinhVien).Where(e => e.QuyenSo == key).ToList();
                }
                else if (indexLoai == 1)
                {
                    //Lớp học phần
                    return context.PhieuThuDbSet.OrderBy(e => e.SinhVien.HoDem).OrderBy(e => e.SinhVien.Ten).Include(e => e.SinhVien).Where(e => e.KC_MaLopHocPhan == key).ToList();
                }
                return null;
            }
        }

        internal static List<PhieuThu> GetDanhSachPhieuThu(string quyenSo, string soPhieu, int? namThu)
        {
            using (var context = new QLHPEntities())
            {
                //Quyển số
                var danhSach = context.PhieuThuDbSet.OrderBy(e => e.SinhVien.HoDem).OrderBy(e => e.SinhVien.Ten).Include(e => e.SinhVien).Where(e => e.QuyenSo == quyenSo);
                if (!string.IsNullOrEmpty(soPhieu))
                    danhSach = danhSach.Where(e => e.SoPhieuThu == soPhieu);
                if (namThu != 0)
                    danhSach = danhSach.Where(e => e.NgayThu.Year == namThu);
                return danhSach.ToList();
            }
        }

        internal static List<long> GetDanhSachQuyenSo()
        {
            using (var context = new QLHPEntities())
            {
                var lstGroupQuyenSo = from pt in context.PhieuThuDbSet
                                      group pt by pt.QuyenSo into QuyenSoGroup
                                      orderby QuyenSoGroup.Key ascending
                                      select new { QuyenSo = QuyenSoGroup.Key };
                var lstQuyenSo = new List<long>();
                foreach (var group in lstGroupQuyenSo)
                    lstQuyenSo.Add(long.Parse(group.QuyenSo.ToString()));
                lstQuyenSo.Sort();
                return lstQuyenSo;
            }
        }

        internal static bool UpdatePhieuThu(PhieuThu phieuThu)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var pt = context.PhieuThuDbSet.Where(e => e.MaPhieuThu.Equals(phieuThu.MaPhieuThu)).FirstOrDefault();
                    pt.QuyenSo = phieuThu.QuyenSo;
                    pt.SoPhieuThu = phieuThu.SoPhieuThu;
                    pt.SoTien = phieuThu.SoTien;
                    pt.NgayThu = phieuThu.NgayThu;
                    pt.NoiDungThu = phieuThu.NoiDungThu;
                    if (!string.IsNullOrEmpty(phieuThu.KC_MaLopHocPhan))
                        pt.KC_MaLopHocPhan = phieuThu.KC_MaLopHocPhan;
                    if (!string.IsNullOrEmpty(phieuThu.MaSinhVien))
                        pt.MaSinhVien = phieuThu.MaSinhVien;
                    context.SaveChanges();
                    return true;
                }
            }
            catch
            {
                throw;
            }
        }

        internal static List<PhieuThu> GetDanhSachPhieuThuTheoThoiGian(string maTruong, int namTuyenSinh, string namHoc, int hocKy, DateTime tu, DateTime den)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var list = (from e in context.PhieuThuDbSet
                                join ee in context.LopHocPhanDbSet on e.KC_MaLopHocPhan equals ee.MaLopHocPhan
                                where
                                (
                                e.SinhVien.Lop.NamTuyenSinh == namTuyenSinh &&
                                e.SinhVien.Lop.Truong.MaTruong == maTruong &&
                                ee.HocKy.NamHoc == namHoc &&
                                ee.HocKy.SoHieuHocKy == hocKy &&
                                e.NgayThu >= tu &&
                                e.NgayThu <= den
                                )
                                select e).Include(t => t.SinhVien).ToList();
                    return list;
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Lấy danh sách phiếu thu từ ngày đến ngày theo trang
        /// </summary>
        /// <param name="tu"></param>
        /// <param name="den"></param>
        /// <returns></returns>
        internal static List<PhieuThu> GetDanhSachPhieuThuTheoThoiGian(DateTime tu, DateTime den, int get, int skip)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var list = (from e in context.PhieuThuDbSet
                                join ee in context.LopHocPhanDbSet on e.KC_MaLopHocPhan equals ee.MaLopHocPhan
                                where
                                (
                                    e.NgayThu >= tu &&
                                    e.NgayThu <= den
                                )
                                orderby e.SinhVien.Lop.MaTruong ascending, e.SinhVien.KC_MaLop ascending, e.SinhVien.Ten ascending
                                //orderby e.NgayThu
                                select e).Include(t => t.SinhVien).Include(t => t.SinhVien.Lop.Truong).Include(t => t.NguoiSuDung).Skip((skip - 1) * get).Take(get);
                    return list.ToList();
                }
            }
            catch
            {
                throw;
            }
        }

        internal static List<PhieuThu> GetDanhSachPhieuThuTheoThoiGian(DateTime tu, DateTime den, NguoiSuDung nguoiThu, int get, int skip)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var list = (from e in context.PhieuThuDbSet
                                join ee in context.LopHocPhanDbSet on e.KC_MaLopHocPhan equals ee.MaLopHocPhan
                                where
                                (
                                    e.NgayThu >= tu &&
                                    e.NgayThu <= den &&
                                    e.NguoiSuDung.TenDangNhap == nguoiThu.TenDangNhap
                                )
                                orderby e.SinhVien.Lop.MaTruong ascending, e.SinhVien.KC_MaLop ascending, e.SinhVien.Ten ascending
                                //orderby e.NgayThu
                                select e).Include(t => t.SinhVien).Include(t => t.SinhVien.Lop.Truong).Include(t => t.NguoiSuDung).Skip((skip - 1) * get).Take(get);
                    return list.ToList();
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Lấy danh sách phiếu thu theo ngày không phân trang
        /// </summary>
        /// <param name="tu"></param>
        /// <param name="den"></param>
        /// <returns></returns>
        internal static List<PhieuThu> GetDanhSachPhieuThuTheoThoiGian(DateTime tu, DateTime den)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var list = (from e in context.PhieuThuDbSet
                                join ee in context.LopHocPhanDbSet on e.KC_MaLopHocPhan equals ee.MaLopHocPhan
                                where
                                (
                                    e.NgayThu >= tu &&
                                    e.NgayThu <= den
                                )
                                orderby e.SinhVien.Lop.MaTruong ascending, e.SinhVien.KC_MaLop ascending, e.SinhVien.Ten ascending

                                select e).Include(t => t.SinhVien).Include(t => t.SinhVien.Lop.Truong).Include(t => t.NguoiSuDung);
                    return list.ToList();
                }
            }
            catch
            {
                throw;
            }
        }

        internal static bool Delete(PhieuThu phieuThu)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var pt = context.PhieuThuDbSet.FirstOrDefault(e => e.MaPhieuThu == phieuThu.MaPhieuThu);
                    context.PhieuThuDbSet.Remove(pt);
                    context.SaveChanges();
                    return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

        internal static string MaxQuyenSo()
        {
            using (var context = new QLHPEntities())
            {
                long PhieuThuMax = 0;
                try
                {
                    var lst = context.Database.SqlQuery<long>
                        ("select max(cast(QuyenSo as bigint)) from PhieuThu").ToList();
                    if (lst.Count > 0)
                        PhieuThuMax = lst[0];
                    else
                        PhieuThuMax = 1;
                    return PhieuThuMax.ToString();
                }
                catch
                {
                    return "1";
                }
            }
        }

        internal static decimal GetTongTienTheoThoiGian(DateTime tu, DateTime den)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var list = (from e in context.PhieuThuDbSet
                                join ee in context.LopHocPhanDbSet on e.KC_MaLopHocPhan equals ee.MaLopHocPhan
                                where
                                (
                                    e.NgayThu >= tu &&
                                    e.NgayThu <= den
                                )
                                //orderby e.SinhVien.Lop.MaTruong ascending, e.SinhVien.KC_MaLop ascending, e.SinhVien.Ten ascending
                                //orderby e.NgayThu
                                select e);
                    return list.Sum(t => t.SoTien);
                }
            }
            catch
            {
                return 0;
            }
        }

        internal static int GetSoBanGhi(DateTime tu, DateTime den)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var list = (from e in context.PhieuThuDbSet
                                join ee in context.LopHocPhanDbSet on e.KC_MaLopHocPhan equals ee.MaLopHocPhan
                                where
                                (
                                    e.NgayThu >= tu &&
                                    e.NgayThu <= den
                                )
                                //orderby e.SinhVien.Lop.MaTruong ascending, e.SinhVien.KC_MaLop ascending, e.SinhVien.Ten ascending
                                //orderby e.NgayThu
                                select e);
                    return list.Count();
                }
            }
            catch
            {
                return 0;
            }
        }

        internal static int GetSoBanGhi(DateTime tu, DateTime den, NguoiSuDung nguoiThu)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var list = (from e in context.PhieuThuDbSet
                                join ee in context.LopHocPhanDbSet on e.KC_MaLopHocPhan equals ee.MaLopHocPhan
                                where
                                (
                                    e.NgayThu >= tu &&
                                    e.NgayThu <= den &&
                                    e.NguoiSuDung.TenDangNhap == nguoiThu.TenDangNhap
                                )
                                //orderby e.SinhVien.Lop.MaTruong ascending, e.SinhVien.KC_MaLop ascending, e.SinhVien.Ten ascending
                                //orderby e.NgayThu
                                select e);
                    return list.Count();
                }
            }
            catch
            {
                return 0;
            }
        }

        internal static decimal GetTongTienTheoThoiGian(DateTime tu, DateTime den, NguoiSuDung nguoiThu)
        {
            try
            {
                using (var context = new QLHPEntities())
                {
                    var list = (from e in context.PhieuThuDbSet
                                join ee in context.LopHocPhanDbSet on e.KC_MaLopHocPhan equals ee.MaLopHocPhan
                                where
                                (
                                    e.NgayThu >= tu &&
                                    e.NgayThu <= den &&
                                    e.NguoiSuDung.TenDangNhap == nguoiThu.TenDangNhap
                                )
                                //orderby e.SinhVien.Lop.MaTruong ascending, e.SinhVien.KC_MaLop ascending, e.SinhVien.Ten ascending
                                //orderby e.NgayThu
                                select e);
                    return list.Sum(t => t.SoTien);
                }
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="listTitle"></param>
        /// <param name="maTruong"></param>
        /// <param name="namTuyenSinh"></param>
        /// <param name="tinhTrangThongke">1: chưa nộp, 2: đã nộp, 3: cả hai</param>
        /// <param name="colFrom">Từ học kỳ</param>
        /// <param name="colTo">Đến học kỳ</param>
        /// <param name="tongCanNop"></param>
        /// <param name="tongDaNop"></param>
        /// <param name="loaiSinhVien"></param>
        /// <returns></returns>
        internal static List<ThongKe> GetThongKe(string[] listTitle, string maTruong, int namTuyenSinh, int tinhTrangThongke, int colFrom, int colTo, ref decimal[] tongCanNop, ref decimal[] tongDaNop, LoaiSinhVien loaiSinhVien)
        {
            using (var context = new QLHPEntities())
            {
                List<ThongKe> dsThongKe = new List<ThongKe>();
                List<SinhVien> listSV = new List<SinhVien>();
                if (loaiSinhVien == LoaiSinhVien.SinhVienKhongChuyen)
                    listSV = context.SinhVienDbSet.Where(e => e.Lop.MaTruong == maTruong && e.Lop.NamTuyenSinh == namTuyenSinh).OrderBy(e => e.KC_MaLop).ToList();
                else
                    listSV = context.SinhVienDbSet.Where(e => e.MaLoai == (int)loaiSinhVien && e.Lop.NamTuyenSinh == namTuyenSinh).OrderBy(e => e.Ten).ToList();
                foreach (var item in listSV)
                {
                    if (item.MaSinhVien == "08VH9010017")
                    {
                        int a = 5;
                        int b = a;
                    }
                    ThongKe tk = new ThongKe();
                    tk.MaSinhVien = item.MaSinhVien;
                    tk.LopGoc = item.Lop.TenLop;
                    tk.Ho = item.HoDem;
                    tk.Ten = item.Ten;
                    var listHp_SV = from t in context.LopHocPhan_SinhVienDbSet
                                    orderby t.LopHocPhan.HocKy.NamHoc, t.LopHocPhan.HocKy.SoHieuHocKy ascending
                                    where (
                                    (t.MaSinhVien == item.MaSinhVien) &&
                                        //Chưa nộp
                                    (tinhTrangThongke == 1 ? (string.IsNullOrEmpty(t.MaPhieuThu) && t.HeSoMienGiam < 1) :
                                        //Đã nộp
                                    tinhTrangThongke == 2 ? ((!string.IsNullOrEmpty(t.MaPhieuThu)) || (t.HeSoMienGiam == 1)) : true
                                    ))
                                    select t;
                    if (listHp_SV.ToList().Count == 0)
                        continue;
                    decimal[] arrDaNop = new decimal[10];
                    decimal[] arrHocPhi = new decimal[10] { -1, -1, -1, -1, -1, -1, -1, -1, -1, -1 };
                    int hk = 0;
                    string ghiChu = "";
                    bool daCongPhuThu = false;
                    foreach (var itemHP_SV in listHp_SV)
                    {
                        if (!string.IsNullOrEmpty(itemHP_SV.GhiChu))
                            if (string.IsNullOrEmpty(ghiChu))
                                ghiChu = itemHP_SV.GhiChu;
                            else
                                ghiChu += ";" + itemHP_SV.GhiChu;
                        hk = Col(listTitle, itemHP_SV.LopHocPhan.HocKy.NamHoc, itemHP_SV.LopHocPhan.HocKy.SoHieuHocKy, loaiSinhVien);
                        if (hk < colFrom || hk > colTo)
                        {
                            arrHocPhi[hk] = -2;
                            continue;
                        }
                        var hp = GetHocPhi(itemHP_SV.LopHocPhan.HocPhi, itemHP_SV.HeSoMienGiam);
                        arrHocPhi[hk] = arrHocPhi[hk] == -1 ? hp : arrHocPhi[hk] + hp;
                        tongCanNop[hk] += hp;
                        if (loaiSinhVien == LoaiSinhVien.SinhVienKhongChuyen && hk == 5 && !daCongPhuThu)
                        {
                            arrHocPhi[hk] += itemHP_SV.LopHocPhan.HocKy.PhuThuKhongChuyen;
                            tongCanNop[hk] += itemHP_SV.LopHocPhan.HocKy.PhuThuKhongChuyen;
                        }
                        else if (loaiSinhVien == LoaiSinhVien.SinhVienChuyenNganh && hk == 9 && !daCongPhuThu)
                        {
                            arrHocPhi[hk] += itemHP_SV.LopHocPhan.HocKy.PhuThuChuyen;
                            tongCanNop[hk] += itemHP_SV.LopHocPhan.HocKy.PhuThuChuyen;
                        }
                        if (tinhTrangThongke != 1)
                        {
                            if (!string.IsNullOrEmpty(itemHP_SV.MaPhieuThu))
                            {
                                var phieuThu = context.PhieuThuDbSet.FirstOrDefault(e => e.MaPhieuThu.Equals(itemHP_SV.MaPhieuThu));
                                if (phieuThu != null)
                                {
                                    arrDaNop[hk] += phieuThu.SoTien;
                                    tongDaNop[hk] += phieuThu.SoTien;
                                }
                            }
                        }
                    }
                    var choIn = true;
                    for (int i = 1; i < colFrom; i++)
                        arrHocPhi[i] = -2;
                    for (int i = colTo + 1; i <= 9; i++)
                        arrHocPhi[i] = -2;

                    if (tinhTrangThongke != 3)
                    {
                        for (int i = 1; i <= 9; i++)
                            if (arrHocPhi[i] == -1)
                                arrHocPhi[i] = -2;
                        if (tinhTrangThongke == 1)
                        {
                            for (int i = 1; i <= 5; i++)
                            {
                                if (arrDaNop[i] == arrHocPhi[i])
                                    arrHocPhi[i] = -2;
                            }
                            if (loaiSinhVien == LoaiSinhVien.SinhVienChuyenNganh)
                                for (int i = 6; i <= 9; i++)
                                {
                                    if (arrDaNop[i] == arrHocPhi[i])
                                        arrHocPhi[i] = -2;
                                }
                        }
                        else if (tinhTrangThongke == 2)
                        {
                            for (int i = colFrom; i <= colTo; i++)
                            {
                                if (arrDaNop[i] != arrHocPhi[i])
                                {
                                    choIn = false;
                                    break;
                                }
                            }
                        }
                    }
                    if (choIn == false)
                        continue;
                    tk.HKI = arrHocPhi[1] == -2 ? "" : arrHocPhi[1] == -1 ? "CĐK" : arrHocPhi[1] == 0 ? "Miễn" : arrDaNop[1].ToString().FormatCurrency() + "/" + arrHocPhi[1].ToString().FormatCurrency();
                    tk.HKII = arrHocPhi[2] == -2 ? "" : arrHocPhi[2] == -1 ? "CĐK" : arrHocPhi[2] == 0 ? "Miễn" : arrDaNop[2].ToString().FormatCurrency() + "/" + arrHocPhi[2].ToString().FormatCurrency();
                    tk.HKIII = arrHocPhi[3] == -2 ? "" : arrHocPhi[3] == -1 ? "CĐK" : arrHocPhi[3] == 0 ? "Miễn" : arrDaNop[3].ToString().FormatCurrency() + "/" + arrHocPhi[3].ToString().FormatCurrency();
                    tk.HKIV = arrHocPhi[4] == -2 ? "" : arrHocPhi[4] == -1 ? "CĐK" : arrHocPhi[4] == 0 ? "Miễn" : arrDaNop[4].ToString().FormatCurrency() + "/" + arrHocPhi[4].ToString().FormatCurrency();
                    tk.HKV = arrHocPhi[5] == -2 ? "" : arrHocPhi[5] == -1 ? "CĐK" : arrHocPhi[5] == 0 ? "Miễn" : arrDaNop[5].ToString().FormatCurrency() + "/" + arrHocPhi[5].ToString().FormatCurrency();
                    if (loaiSinhVien == LoaiSinhVien.SinhVienChuyenNganh)
                    {
                        tk.HKVI = arrHocPhi[6] == -2 ? "" : arrHocPhi[6] == -1 ? "CĐK" : arrHocPhi[6] == 0 ? "Miễn" : arrDaNop[6].ToString().FormatCurrency() + "/" + arrHocPhi[6].ToString().FormatCurrency();
                        tk.HKVII = arrHocPhi[7] == -2 ? "" : arrHocPhi[7] == -1 ? "CĐK" : arrHocPhi[7] == 0 ? "Miễn" : arrDaNop[7].ToString().FormatCurrency() + "/" + arrHocPhi[7].ToString().FormatCurrency();
                        tk.HKVIII = arrHocPhi[8] == -2 ? "" : arrHocPhi[8] == -1 ? "CĐK" : arrHocPhi[8] == 0 ? "Miễn" : arrDaNop[8].ToString().FormatCurrency() + "/" + arrHocPhi[8].ToString().FormatCurrency();
                        tk.HKIX = arrHocPhi[9] == -2 ? "" : arrHocPhi[9] == -1 ? "CĐK" : arrHocPhi[9] == 0 ? "Miễn" : arrDaNop[9].ToString().FormatCurrency() + "/" + arrHocPhi[9].ToString().FormatCurrency();
                    }
                    if (loaiSinhVien == LoaiSinhVien.SinhVienKhongChuyen)
                    {
                        if (string.IsNullOrEmpty(tk.HKI) &&
                            string.IsNullOrEmpty(tk.HKII) &&
                            string.IsNullOrEmpty(tk.HKIII) &&
                            string.IsNullOrEmpty(tk.HKIV) &&
                            string.IsNullOrEmpty(tk.HKV))
                            continue;
                    }
                    else
                        if (string.IsNullOrEmpty(tk.HKI) &&
                            string.IsNullOrEmpty(tk.HKII) &&
                            string.IsNullOrEmpty(tk.HKIII) &&
                            string.IsNullOrEmpty(tk.HKIV) &&
                            string.IsNullOrEmpty(tk.HKV) &&
                            string.IsNullOrEmpty(tk.HKVI) &&
                            string.IsNullOrEmpty(tk.HKVII) &&
                            string.IsNullOrEmpty(tk.HKVIII) &&
                            string.IsNullOrEmpty(tk.HKIX))
                            continue;
                    tk.GhiChu = ghiChu;
                    dsThongKe.Add(tk);
                }
                return dsThongKe;
            }
        }

        /// <summary>
        /// Trả về thứ tự của cột
        /// </summary>
        /// <param name="listTitle"></param>
        /// <param name="namHoc"></param>
        /// <param name="hocKy"></param>
        /// <returns></returns>
        internal static int Col(string[] listTitle, string namHoc, int hocKy, LoaiSinhVien loaiSV)
        {
            for (int i = 0; i < listTitle.Count(); i++)
            {
                string temp = string.Format("HK{0}/{1}", hocKy == 1 ? "I" : "II", namHoc);
                if (listTitle[i] == temp)
                    return i + 1;
            }
            if (loaiSV == LoaiSinhVien.SinhVienKhongChuyen)
                return 5;
            else
                return 9;
        }

        /// <summary>
        /// Tính học phí cần nộp
        /// </summary>
        /// <param name="hocPhiThucTe"></param>
        /// <param name="heSoMienGiam"></param>
        /// <returns></returns>
        internal static decimal GetHocPhi(decimal hocPhiThucTe, double heSoMienGiam)
        {
            if (heSoMienGiam == 0)
                return
                    hocPhiThucTe;
            return hocPhiThucTe * (1 - (decimal)heSoMienGiam);
        }
    }

    class ThongKe
    {
        public string MaSinhVien { get; set; }
        public string Ho { get; set; }
        public string Ten { get; set; }
        public string LopGoc { get; set; }
        public string HKI { get; set; }
        public string HKII { get; set; }
        public string HKIII { get; set; }
        public string HKIV { get; set; }
        public string HKV { get; set; }

        public string HKVI { get; set; }
        public string HKVII { get; set; }
        public string HKVIII { get; set; }
        public string HKIX { get; set; }
        public string GhiChu { get; set; }
    }
}
